########################################################################
#  Copyright (C) 2009 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
########################################################################
"""Convert BLL investment portfolio position XLS file to flat-OFX-RDF and
load into Sesame server.
"""

from csv2flt import *
import re
from xls2csv2 import xls2csv2
from n3tagvalue import *

def acctidre(name):
    return '(?P<%s>[\d\-\/]+)'%name

def secid(name):
    return '(?P<%s>\d{6,7})'%name

htf_list=[
#version 0
([
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xe1\xe8 \xfa\xe9\xf7', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Portfolio view
    ['('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', '', '', '', '', '',
     '', '', '', '('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8',
     '\xf9\xed \xf0\xe9\xe9\xf8', 
     '\xe0\xf8\xe5\xf2 \xeb\xf1\xf4\xe9', 
     '\xf9\xf2\xf8 \xf7\xf0\xe9\xe4 \xee\xee\xe5\xf6\xf2', 
     '\xeb\xee\xe5\xfa \xe0\xe7\xe6\xf7\xe4', 
     '\xf9\xe5\xe5\xe9 \xe0\xe7\xe6\xf7\xe4', 
     '\xf9\xf2\xf8', 
     '\xe0\xe7\xe5\xe6 \xf9\xe9\xf0\xe5\xe9 \xe9\xe5\xee\xe9', 
     '\xf8\xe5\xe5\xe7 \xe1-%', 
     '\xf8\xe5\xe5\xe7 \xe1\xf9\xe7', 
     '\xf9\xed \xfa\xe9\xf7']
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 '(0\.0)|(\xec\xe0 \xf7\xe9\xe9\xed)',
 pvaluere('AVG_COST',1),
 pvaluere('UNITS',1),
 pvaluere('MKTVAL',1),
 pvaluere('UNITPRICE',1), # Surprisngly this can be in different currencies for different assets and is for 100 Units for bonds
 floatre('DAY_CHANGE_PERCENT'),
 floatre('CHANGE_PERCENT'),
 valuere('CGAIN_ILS',1),
 acctidre('ACCTID')],
None),
#version1
([
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xe1\xe8 \xe0\xe9\xf9\xe9 - \xe4\xe0\xe7\xe6\xf7\xe5\xfa \xf9\xec\xe9',
     '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Portfolio view
    ['\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/'), '',':\xfa\xe9\xf7',
     acctidre('ACCTID'), '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8',
     '\xf9\xed \xf0\xe9\xe9\xf8',
     '\xe0\xf8\xe5\xf2 \xeb\xf1\xf4\xe9',
     '\xf9\xf2\xf8 \xf7\xf0\xe9\xe4 \xee\xee\xe5\xf6\xf2',
     '\xeb\xee\xe5\xfa \xe0\xe7\xe6\xf7\xe4',
     '\xf9\xe5\xe5\xe9 \xe0\xe7\xe6\xf7\xe4',
     '% \xee\xe4\xfa\xe9\xf7', '\xf9\xf2\xf8',
     '% \xf9\xe9\xf0\xe5\xe9 \xe9\xe5\xee\xe9',
     '\xf8\xe5\xe5\xe7 \xe1-%',
     "\xf8\xe5\xe5\xe7 \xe1\xf9''\xe7"]
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 '(0\.0)|((\xec\xe0 )?\xf7\xe9\xe9\xed)',
 pvaluere('AVG_COST',1),
 pvaluere('UNITS',1),
 pvaluere('MKTVAL',1),
 pvaluere('PART_OF_PORTFOLIO',1),
 pvaluere('UNITPRICE',1), # Surprisngly this can be in different currencies for different assets and is for 100 Units for bonds
 floatre('DAY_CHANGE_PERCENT'),
 floatre('CHANGE_PERCENT'),
 valuere('CGAIN_ILS',1),
 ],
None),
#version2
([
    ['', '', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xe1\xe8 \xe0\xe9\xf9\xe9 - \xe4\xe0\xe7\xe6\xf7\xe5\xfa \xf9\xec\xe9',
     '', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Portfolio view
    ['\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/'), '',':\xfa\xe9\xf7',
     acctidre('ACCTID'), '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8',
     '\xf9\xed \xf0\xe9\xe9\xf8',
     '\xe0\xf8\xe5\xf2 \xeb\xf1\xf4\xe9',
     '\xf9\xf2\xf8 \xf7\xf0\xe9\xe4 \xee\xee\xe5\xf6\xf2',
     '\xeb\xee\xe5\xfa \xe0\xe7\xe6\xf7\xe4',
     '\xf9\xe5\xe5\xe9 \xe0\xe7\xe6\xf7\xe4',
     '% \xee\xe4\xfa\xe9\xf7', '\xf9\xf2\xf8',
     '% \xf9\xe9\xf0\xe5\xe9 \xe9\xe5\xee\xe9',
     '\xf8\xe5\xe5\xe7 \xe1-%',
     "\xf8\xe5\xe5\xe7 \xe1\xf9''\xe7", '\xee\xf1 \xe4\xfa\xe9\xf7']
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 '(0\.0)|((\xec\xe0 )?\xf7\xe9\xe9\xed)',
 pvaluere('AVG_COST',1),
 pvaluere('UNITS',1),
 pvaluere('MKTVAL',1),
 pvaluere('PART_OF_PORTFOLIO',1),
 pvaluere('UNITPRICE',1), # Surprisngly this can be in different currencies for different assets and is for 100 Units for bonds
 floatre('DAY_CHANGE_PERCENT'),
 floatre('CHANGE_PERCENT'),
 valuere('CGAIN_ILS',1),
 acctidre('ACCTID'),
 ],
None),
#version3
([
    ['', '', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xe1\xe8 \xe0\xe9\xf9\xe9 - \xe4\xe0\xe7\xe6\xf7\xe5\xfa \xf9\xec\xe9',
     '', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Portfolio view
    ['\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/'), '',':\xfa\xe9\xf7',
     acctidre('ACCTID'), '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', '', ''],
    ['', '', '', '', '', '', '', '', '', '', '', ''],
    ['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8',
     '\xf9\xed \xf0\xe9\xe9\xf8',
     '\xe0\xf8\xe5\xf2 \xeb\xf1\xf4\xe9',
     '\xf9\xf2\xf8 \xf7\xf0\xe9\xe4',
     '\xeb\xee\xe5\xfa',
     '\xf9\xe5\xe5\xe9',
     '% \xee\xe4\xfa\xe9\xf7', '\xf9\xf2\xf8',
     '% \xf9\xe9\xf0\xe5\xe9',
     '\xf8\xe5\xe5\xe7 \xe1-%',
     '\xf8\xe5\xe5\xe7',
     '\xee\xf1 \xe4\xfa\xe9\xf7'],
    ['', '', '', '\xee\xee\xe5\xf6\xf2', '\xe0\xe7\xe6\xf7\xe4',
     '\xe0\xe7\xe6\xf7\xe4', '', '', '\xe9\xe5\xee\xe9', '',
     '\xe1\xf9"\xe7', ''],
],
[secid('SECID')+'.0',
 strre('SECNAME'),
 '(0\.0)|((\xec\xe0 )?\xf7\xe9\xe9\xed)',
 pvaluere('AVG_COST',1),
 pvaluere('UNITS',1),
 pvaluere('MKTVAL',1),
 pvaluere('PART_OF_PORTFOLIO',1),
 pvaluere('UNITPRICE',1), # Surprisngly this can be in different currencies for different assets and is for 100 Units for bonds
 floatre('DAY_CHANGE_PERCENT'),
 floatre('CHANGE_PERCENT'),
 valuere('CGAIN_ILS',1),
 acctidre('ACCTID'),
 ],
None),

]

def pre(fname,csvfile,hashing=False):
    xls2csv2(fname,csvfile,'hebrew')

def flat(csvfile,fout,context=None,hashing=False):
    n3header(fout,"$Id$"[1:-1],context)
    h,t,f,version=readcsvtable_multiformat(csvfile,htf_list)
    if h==None:
        raise Exception("File does not match template")

    l=h[2]
    dtasof = "%s%s%s"%(l['DTASOF_year'],l['DTASOF_month'],l['DTASOF_day'])
    accttype="INVESTMENT"
    if version==1 or version==2 or version==3:
        acctid=h[2]['ACCTID']

    currate=fltparams.get('CURRATE',[]) if fltparams else []
    if currate:
        currate=float(currate[0])

    for l in t:
        print >>fout,"a3:flatLine [ ",
        units=float(l['UNITS'])
        unitprice=float(l['UNITPRICE'])/100
        mktval=float(l['MKTVAL'])
        if abs(mktval-units*unitprice)>0.5:
            print mktval,units*unitprice

        print >>fout,stagvalue('DTASOF',dtasof),stagvalue('CURDEF','ILS'),
        print >>fout,n3secid('TASE',l['SECID']),
        print >>fout,stagvalue('SECNAME',l['SECNAME'],language="hebrew"),
        if version==0 or version==2:
            acctid=l['ACCTID']
        print >>fout,stagvalue('ACCTID',acctid,hashing=hashing),
        print >>fout,stagvalue('ACCTTYPE',accttype),
        print >>fout,stagvalue('UNITS',units),
        print >>fout,stagvalue('UNITPRICE',unitprice),
        if currate:
            print >>fout,stagvalue('CURRATE',currate)
            mktval/=currate
        print >>fout,stagvalue('MKTVAL',mktval),
        print >>fout,"];"
    print >>fout,"."
